
PROC DATASETS LIB=work KILL;  RUN;
quit;

*** Resets line numbers in log file ****;
resetline;


options ls=150 ps=max nocenter nonumber nodate fullstimer minoperator mlogic;
libname bhcdat 'F:\TDriveCopy\Research\Data\BankData\BHCdata\';
libname struct 'F:\TDriveCopy\Research\Data\WRDSbankdata\';

%let INPUTVARS = RSSD9001 RSSD9999 RSSD9050 RSSD9348 RSSD9210 RSSD9220 RSSD9200 RSSD9424 RSSD9048
				BHCK3815 BHCKB514 BHCKB515 BHCKB575 BHCKB576 BHCKB577 BHCKB538 BHCKB838 BHCKB839 BHCKB840 BHCKB841 BHCKJ455 BHCKJ456 BHCKB707
				BHCB2604 BHCKA223 BHCK0081 BHCK0395	BHCK0397 BHCK1296 BHCK1410 BHCK1590	BHCK1754 BHCK1763 BHCK1764 BHCK1773
                BHCK1975 BHCK2011 BHCK2081 BHCK2122	BHCK2130 BHCK2145 BHCK2146 BHCK2170 BHCK2948 BHCK3123 BHCK3128
                BHCK3210 BHCK3450 BHCK3545 BHCK4074	BHCK4079 BHCK4093 BHCK4107 BHCK4230	BHCK4300 BHCK4340 BHCK4635 BHCK5525	
                BHCK5526 BHCK8723 BHCK8724 BHCK8725	BHCK8726 BHCK8727 BHCK8728 BHDM6631	BHDM6636 BHFN6631 BHFN6636 BHOD2604
                BHCKA126 BHCKA127 BHCK8694 BHCKB538	BHCK7206 BHCK7205 BHCK2008 BHCKB539 BHCK3548 BHCK4062 BHCK2332 BHCK2333
                BHCK2309 BHCK3290 BHCK3293 BHCK4073 BHCKB705 BHCKB706 BHCKB707 BHCKB708 BHCKB709 BHCKB710 BHCKA535 BHCKC968
                BHCKC969 BHCKC970 BHCKC971 BHCKC972 BHCKC973 BHCKC974 BHCKC975 BHCKA534 BHCK3283 BHCK3792 BHCK8274 BHCKB808 
                BHCKF699 BHCKF071 BHCKF070 BHCKF069 BHCKF068 BHCKF067 BHCKF066 BHCKC218 BHCKC217 BHCK5412 BHCKC235 BHCKC234 
                BHCK5411 BHDM1420 BHDM1480 BHCKF160 BHCKF161 BHDM1460 BHDM1797 BHDM5367 BHDM5368 BHCKB529 BHCB2210
                BHCB3187 BHCK2750 BHCK1350 BHDMB987 BHCKB989 BHCK2800 BHDMB993 BHCKB995 BHCK4010 BHCK4435 BHCK4436 BHCKF821
                BHCK4020 BHCK4135 BHCKA224 BHCKA222 BHCKB696 BHCKB697 BHCKB698 BHCKB699 BHCK3816 BHCK6550  
				RSSD9050 RSSD9348 RSSD9210 RSSD9220 RSSD9200 RSSD9424 RSSD9048 RSSD9016 RSSD9045 RSSD9057 BHCK3547
				RSSD9061 RSSD9060 RSSD9161 RSSD9132 RSSD9325 RSSD9329 RSSD9199 BHDMA243 BHDMA164 BHDMA242 BHFNA245;

*** bhcdata0217 is the Y9C data downloaded from WRDS from 2002 and 2017 and appended together to form bhcdata0217 ***;
data bhcdata0217STWFVars;
retain DATE RSSD_ID;
set bhcdata0217(KEEP=&INPUTVARS);

RSSD_ID 			= RSSD9001; * For permco rssd_id match ****;
DATE				= RSSD9999;
YEAR				=INT(DATE/10000);
MONTH				=INT(DATE/100)-YEAR*100; 
CERT				=RSSD9050;  * FDIC CERTIFICATE ID;
REG_HIGH_HOLD		=RSSD9348;
LOCATION			=RSSD9210;    * TWO DIGITS CODE ;
ZIPCODE				=RSSD9220; 
STATE               =RSSD9200;  * TWO LETTERS ABBREVIATIONS;
INSURED             =RSSD9424;    * INSURED BANK INDICATOR RSSD9424=1 (FDIC/BIF), 2 (FDIC/SAIF) OR 6 (FDIC/BIF & FDIC/SAIF);
CHARTER    			=RSSD9048;    * =200 CHARTERED AS COMMERCIAL BANKS;

TOT_LOANS_LEASES						= BHCK2122; 
TOT_ASSETS								 = BHCK2170; 
TOT_DEPOSITS							= sum(BHDM6631,BHDM6636,BHFN6631,BHFN6636); *** Domestic + Foreign deposits ***;


FOREIGN_DEPOSITS						= sum(BHFN6631,BHFN6636);
LARGE_DEPOSITS							= sum(BHCB2604,BHOD2604);
otherborrowedmoney_lt1yr				= BHCK2332;
otherborrowedmoney_gt1yr				= BHCK2333;

FEDF_PURCH_ONLY = BHDMB993; **** from 2002Q1 ****; 
REPO = BHCKB995;  **** from 2002Q1 ****;
CP = BHCK2309;

fedfundsrepoliab=sum(BHDMB993,BHCKB995); /* 200203- */

timedepge100k_lt1yr=BHDMA242; /*TIME DEPOSITS OF $100,000 OR MORE WITH A REMAINING MATURITY OF ONE YEAR OR LESS (1996-03-31 to 2016-12-31)*/
foreigntimedep_lt1yr=BHFNA245; /*TIME DEPOSITS IN FOREIGN OFFICES WITH A REMAINING MATURITY OF ONE YEAR OR LESS (2001-03-31 to --)*/

tradingliab_exclreval=sum(BHCK3548,-BHCK3547);
tradingliabilities=BHCK3548; /* 199403- */

TOT_LIABILITIES	= BHCK2948;


  NET_RISK_WT_ASSETS					= BHCKA223; *Reported after 03/31/1996;
  TOT_RISK_BASED_CAP					= BHCK3792;	*Reported after 03/31/1996;


  NET_INCOME							= BHCK4340;
  EQUITY_CAPITAL						= BHCK3210;



  LOANS_LATE_90D_INCL_SEC				= BHCK5525;						*Reported after 09/30/1990;
  LOANS_NOT_ACCR_INCL_SEC				= BHCK5526;						*Reported after 09/30/1990;
  NONPERF_LOANS							= sum(LOANS_LATE_90D_INCL_SEC,LOANS_NOT_ACCR_INCL_SEC);		*Valid after 09/30/1990;

  ALW_LOAN_LEASES_LOSSES				= BHCK3123; 
  ALLOC_TRANS_RISK_RESERVES 			= BHCK3128;
  CHARGEOFFS							= BHCK4635;
  PROVISION_LOANS_LOSSES				= BHCK4230;


  CC_SOLD_SEC 						=BHCKB707; ** OUTSTANDING PRINCIPAL BALANCE OF CREDIT CARDS SOLD AND SECURITIZED ***;

  if date<20100331 then UNUSED_CC_COMMIT=BHCK3815; ** UNUSED COMMITMENTS - CREDIT CARD LINES; 
	else UNUSED_CC_COMMIT=sum(BHCKJ455,BHCKJ456);


  TOT_CC_LOANS 						= BHCKB538; ** CREDIT CARDS LOANS FROM SCHEDULE HC-C;
  MORTGAGE_LOANS					= BHCK1410;


run;


data bhcdata0217STWFVars; set bhcdata0217STWFVars;
proc sort nodupkey; by RSSD_ID DATE; *** 0 dups deleted ***;
run;

	

proc export data=bhcdata0217STWFVars
outfile="F:\TDriveCopy\Research\Data\Y9CDataForBHCs.dta" replace;
run;
